package com.gs.synedocket.db.sqlite;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.gs.synedocket.constant.AllConstants;
import com.gs.synedocket.db.dto.DocketAccRepDto;
import com.gs.synedocket.db.dto.DocketAddChargeDto;
import com.gs.synedocket.db.dto.DocketAdditionalDto;
import com.gs.synedocket.db.dto.DocketDto;
import com.gs.synedocket.db.dto.DocketQcDto;
import com.gs.synedocket.db.dto.DocketSiteDto;
import com.gs.synedocket.db.dto.MenuDto;
import com.gs.synedocket.db.dto.ShortDto;
import com.gs.synedocket.db.entity.Admixture;
import com.gs.synedocket.db.entity.Cement;
import com.gs.synedocket.db.entity.Customer;
import com.gs.synedocket.db.entity.Docket;
import com.gs.synedocket.db.entity.DocketAccRepPhoto;
import com.gs.synedocket.db.entity.DocketAdmixture;
import com.gs.synedocket.db.entity.DocketPhoto;
import com.gs.synedocket.db.entity.Mix;
import com.gs.synedocket.db.entity.ParamType;
import com.gs.synedocket.db.entity.Plant;
import com.gs.synedocket.db.entity.Product;
import com.gs.synedocket.db.entity.Site;
import com.gs.synedocket.db.entity.Team;
import com.gs.synedocket.db.entity.Truck;
import com.gs.synedocket.db.entity.UserTruck;
import com.gs.synedocket.util.AppUtil;
import com.gs.synedocket.util.DateUtil;
import com.gs.synedocket.util.LanguageUtils;
import com.gs.synedocket.util.SPUtil;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DBManager {
    private static volatile DBManager instance;
    public static SQLiteDatabase db;

    private DBManager() {
    }

    public static DBManager getInstance() {
        if (instance == null) {
            synchronized (DBManager.class) {
                if (instance == null) {
                    instance = new DBManager();
                }
            }
        }
        return instance;
    }

    public void init() {
        if (db == null) {
            DBHelper mDBHelper = new DBHelper(AppUtil.getInstance().getContext());
            db = mDBHelper.getWritableDatabase();
        }
    }

    public void beginTransaction() {
        db.beginTransaction();
    }

    public void commitTransaction() {
        db.setTransactionSuccessful();
    }

    public void endTransaction() {
        db.endTransaction();
    }

    public void insertAdmixture(Admixture bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.insert(DBHelper.TABLE_ADMIXTURE, null, values);
    }

    public void updateAdmixture(Admixture bean) {
        ContentValues values = new ContentValues();
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.update(DBHelper.TABLE_ADMIXTURE, values, "id = '" + bean.getId() + "'", null);
    }

    public void insertCement(Cement bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.insert(DBHelper.TABLE_CEMENT, null, values);
    }

    public void updateCement(Cement bean) {
        ContentValues values = new ContentValues();
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.update(DBHelper.TABLE_CEMENT, values, "id = '" + bean.getId() + "'", null);
    }

    public void insertCustomer(Customer bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("contacter", bean.getContacter());
        values.put("tel", bean.getTel());
        values.put("state", bean.getState());
        values.put("flag", bean.getFlag());
        db.insert(DBHelper.TABLE_CUSTOMER, null, values);
    }

    public void updateCustomer(Customer bean) {
        ContentValues values = new ContentValues();
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("contacter", bean.getContacter());
        values.put("tel", bean.getTel());
        values.put("state", bean.getState());
        values.put("flag", bean.getFlag());
        db.update(DBHelper.TABLE_CUSTOMER, values, "id = '" + bean.getId() + "'", null);
    }

    public void insertMix(Mix bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.insert(DBHelper.TABLE_MIX, null, values);
    }

    public void updateMix(Mix bean) {
        ContentValues values = new ContentValues();
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.update(DBHelper.TABLE_MIX, values, "id = '" + bean.getId() + "'", null);
    }


    public void insertPlant(Plant bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.insert(DBHelper.TABLE_PLANT, null, values);
    }

    public void updatePlant(Plant bean) {
        ContentValues values = new ContentValues();
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.update(DBHelper.TABLE_PLANT, values, "id = '" + bean.getId() + "'", null);
    }

    public List<ShortDto> queryPlantAsShort() {
        String name = LanguageUtils.getInstance().getNameFromLan();
        List<ShortDto> list = new ArrayList<>();
        try (Cursor cursor = db.query(DBHelper.TABLE_PLANT, new String[]{"id", name + " AS name", "code"}, null, null, null, null, null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getShortDto(cursor));
                }
            }
        }
        return list;
    }

    public void insertProduct(Product bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.insert(DBHelper.TABLE_PRODUCT, null, values);
    }

    public void updateProduct(Product bean) {
        ContentValues values = new ContentValues();
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.update(DBHelper.TABLE_PRODUCT, values, "id = '" + bean.getId() + "'", null);
    }

    public void insertSite(Site bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("emails", bean.getEmails());
        values.put("gps", bean.getGps());
        values.put("state", bean.getState());
        db.insert(DBHelper.TABLE_SITE, null, values);
    }

    public void updateSite(Site bean) {
        ContentValues values = new ContentValues();
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("emails", bean.getEmails());
        values.put("gps", bean.getGps());
        values.put("state", bean.getState());
        db.update(DBHelper.TABLE_SITE, values, "id = '" + bean.getId() + "'", null);
    }

    public List<ShortDto> querySiteAsShort() {
        String name = LanguageUtils.getInstance().getNameFromLan();
        List<ShortDto> list = new ArrayList<>();
        try (Cursor cursor = db.query(DBHelper.TABLE_SITE, new String[]{"id", name + " AS name", "code"},
                null, null, null, null, null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getShortDto(cursor));
                }
            }
        }
        return list;
    }

    public void insertParamType(ParamType bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("flag", bean.getFlag());
        values.put("typeName", bean.getTypeName());
        values.put("orderId", bean.getOrderId());
        values.put("icon", bean.getIcon());
        db.insert(DBHelper.TABLE_PARAM_TYPE, null, values);
    }

    public List<ShortDto> queryParamTypeAsShort() {
        String name = LanguageUtils.getInstance().getNameFromLan();
        List<ShortDto> list = new ArrayList<>();
        try (Cursor cursor = db.query(DBHelper.TABLE_PARAM_TYPE, new String[]{"flag", name + " AS name", "code"},
                "typeName = 'driverJobStates'", null, null, null, null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getParamShortDto(cursor));
                }
            }
        }
        return list;
    }

    private ShortDto getParamShortDto(Cursor cursor) {
        ShortDto bean = new ShortDto();
        bean.setId(cursor.getString(cursor.getColumnIndex("flag")));
        bean.setCode(cursor.getString(cursor.getColumnIndex("code")));
        bean.setName(cursor.getString(cursor.getColumnIndex("name")).trim());
        return bean;
    }

    private ShortDto getShortDto(Cursor cursor) {
        ShortDto bean = new ShortDto();
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        bean.setCode(cursor.getString(cursor.getColumnIndex("code")));
        bean.setName(cursor.getString(cursor.getColumnIndex("name")));
        return bean;
    }

    public void insertTeam(Team bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.insert(DBHelper.TABLE_TEAM, null, values);
    }

    public void updateTeam(Team bean) {
        ContentValues values = new ContentValues();
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.update(DBHelper.TABLE_TEAM, values, "id = '" + bean.getId() + "'", null);
    }

    public void insertTruck(Truck bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("code", bean.getCode());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("state", bean.getState());
        db.insert(DBHelper.TABLE_TRUCK, null, values);
    }

    public List<ShortDto> queryTruckAsShort() {
        String name = LanguageUtils.getInstance().getNameFromLan();
        List<ShortDto> list = new ArrayList<>();
        try (Cursor cursor = db.query(DBHelper.TABLE_TRUCK, new String[]{"id", name + " AS name", "code"},
                " id IN (SELECT truckId FROM " + DBHelper.TABLE_USER_TRUCK + " )", null, null, null, null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getShortDto(cursor));
                }
            }
        }
        return list;
    }

    public void insertUserTruck(UserTruck bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("userId", bean.getUserId());
        values.put("truckId", bean.getTruckId());
        values.put("def", bean.getDef());
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_USER_TRUCK, null, values);
    }

    public void updateUserTruck(UserTruck bean) {
        ContentValues values = new ContentValues();
        values.put("def", bean.getDef());
        values.put("upload", bean.getUpload());
        db.update(DBHelper.TABLE_USER_TRUCK, values, " userId = '" + bean.getUserId() + "' AND truckId = '" + bean.getTruckId() + "'", null);
    }

    public List<UserTruck> queryUserTruck(String limit) {
        List<UserTruck> list = new ArrayList<>();
        try (Cursor cursor = db.query(DBHelper.TABLE_USER_TRUCK, null, " upload = 0 ", null, null, null, null, limit)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getUserTruck(cursor));
                }
            }
        }
        return list;
    }

    private UserTruck getUserTruck(Cursor cursor) {
        UserTruck bean = new UserTruck();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        bean.setUserId(cursor.getString(cursor.getColumnIndex("userId")));
        bean.setTruckId(cursor.getString(cursor.getColumnIndex("truckId")));
        bean.setDef(cursor.getInt(cursor.getColumnIndex("def")) == 1);
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        return bean;
    }

    public List<ShortDto> queryUserTruckAsShort() {
        List<ShortDto> list = new ArrayList<>();
        String name = LanguageUtils.getInstance().getNameFromLan();
        String truckNo = SPUtil.getInstance().getString(AllConstants.TRUCK_NAME);
        try (Cursor cursor = db.rawQuery("SELECT * " +
                " FROM " + DBHelper.TABLE_TRUCK +
                " WHERE id IN ( SELECT truckId FROM " + DBHelper.TABLE_USER_TRUCK + " )", null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getUserTruckAsShort(cursor, name, truckNo));
                }
            }
        }
        return list;
    }

    private ShortDto getUserTruckAsShort(Cursor cursor, String name, String truckNo) {
        ShortDto bean = new ShortDto();
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        bean.setName(cursor.getString(cursor.getColumnIndex(name)));
        bean.setCode(cursor.getString(cursor.getColumnIndex("code")));
        bean.setDef(truckNo.equals(bean.getName()));
        return bean;
    }

    public void insertDocket(Docket bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("docketNo", bean.getDocketNo());
        values.put("docketAt", DateUtil.DateTime2Str(bean.getDocketAt()));
        values.put("contractNo", bean.getContractNo());
        values.put("salesOrder", bean.getSalesOrder());
        values.put("additionalCharge1", bean.getAdditionalCharge1());
        values.put("additionalCharge2", bean.getAdditionalCharge2());
        values.put("siteId", bean.getSiteId());
        values.put("truckNo", bean.getTruckNo());
        values.put("preTruckNo", bean.getPreTruckNo());
        values.put("plantId", bean.getPlantId());
        values.put("customerId", bean.getCustomerId());
        values.put("mixId", bean.getMixId());
        values.put("productId", bean.getProductId());
        values.put("teamId", bean.getTeamId());
        values.put("cementId", bean.getCementId());
        values.put("batchTime", DateUtil.DateTime2Str(bean.getBatchTime()));
        values.put("distance", bean.getDistance());
        values.put("loadQty", bean.getLoadQty());
        values.put("cumQty", bean.getCumQty());
        values.put("orderQty", bean.getOrderQty());
        values.put("lastDriverTel", bean.getLastDriverTel());
        values.put("specialTerm", bean.getSpecialTerm());
        values.put("kWahQRCode", bean.getkWahQRCode());
        values.put("custQRCode", bean.getCustQRCode());
        values.put("authorityQRCode", bean.getAuthorityQRCode());
        values.put("authorityName", bean.getAuthorityName());
        values.put("nominalMaxAgg", bean.getNominalMaxAgg());
        values.put("custPo", bean.getCustPo());
        values.put("slump", bean.getSlump());
        values.put("slump1", bean.getSlump1());
        values.put("slump2", bean.getSlump2());
        values.put("temperature", bean.getTemperature());
        values.put("signPhoto", bean.getSignPhoto());
        values.put("scanPhoto", bean.getScanPhoto());
        values.put("arriveSiteAt", DateUtil.DateTime2Str(bean.getArriveSiteAt()));
        values.put("arriveSiteGps", bean.getArriveSiteGps());
        values.put("arriveSiteGpsLoc", bean.getArriveSiteGpsLoc());
        values.put("startDisAt", DateUtil.DateTime2Str(bean.getStartDisAt()));
        values.put("startDisGps", bean.getStartDisGps());
        values.put("startDisGpsLoc", bean.getStartDisGpsLoc());
        values.put("endDisAt", DateUtil.DateTime2Str(bean.getEndDisAt()));
        values.put("endDisGps", bean.getEndDisGps());
        values.put("endDisGpsLoc", bean.getEndDisGpsLoc());
        values.put("leaveSiteAt", DateUtil.DateTime2Str(bean.getLeaveSiteAt()));
        values.put("leaveSiteGps", bean.getLeaveSiteGps());
        values.put("leaveSiteGpsLoc", bean.getLeaveSiteGpsLoc());
        values.put("arrivePlantAt", DateUtil.DateTime2Str(bean.getArrivePlantAt()));
        values.put("arrivePlantGps", bean.getArrivePlantGps());
        values.put("arrivePlantGpsLoc", bean.getArrivePlantGpsLoc());
        values.put("contactTel", bean.getContactTel());
        values.put("contactPerson", bean.getContactPerson());
        values.put("truckState", bean.getTruckState());
        values.put("state", bean.getState());
        values.put("remark", bean.getRemark());
        values.put("driverRemark", bean.getDriverRemark());
        values.put("changeTruck", bean.getChangeTruck());
        values.put("extraAddress", bean.getExtraAddress());
        values.put("siteCode", bean.getSiteCode());
        values.put("siteName", bean.getSiteName());
        values.put("siteChineseName", bean.getSiteChineseName());
        values.put("siteGPS", bean.getSiteGPS());
        values.put("customerCode", bean.getCustomerCode());
        values.put("custName", bean.getCustName());
        values.put("custChineseName", bean.getCustChineseName());
        values.put("productCode", bean.getProductCode());
        values.put("productName", bean.getProductName());
        values.put("productChineseName", bean.getProductChineseName());
        values.put("createdAt", DateUtil.DateTimeToStr(bean.getCreatedAt(), "yyyy-MM-dd HH:mm:ss"));
        values.put("modifyAt", DateUtil.DateTimeToStr(bean.getModifyAt(), "yyyy-MM-dd HH:mm:ss"));
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_DOCKET, null, values);
    }

    public void updateDocket(DocketDto bean) {
        ContentValues values = new ContentValues();
        values.put("arriveSiteAt", DateUtil.DateTime2Str(bean.getArriveSiteAt()));
        values.put("arriveSiteGps", bean.getArriveSiteGps());
        values.put("arriveSiteGpsLoc", bean.getArriveSiteGpsLoc());
        values.put("startDisAt", DateUtil.DateTime2Str(bean.getStartDisAt()));
        values.put("startDisGps", bean.getStartDisGps());
        values.put("startDisGpsLoc", bean.getStartDisGpsLoc());
        values.put("endDisAt", DateUtil.DateTime2Str(bean.getEndDisAt()));
        values.put("endDisGps", bean.getEndDisGps());
        values.put("endDisGpsLoc", bean.getEndDisGpsLoc());
        values.put("leaveSiteAt", DateUtil.DateTime2Str(bean.getLeaveSiteAt()));
        values.put("leaveSiteGps", bean.getLeaveSiteGps());
        values.put("leaveSiteGpsLoc", bean.getLeaveSiteGpsLoc());
        values.put("arrivePlantAt", DateUtil.DateTime2Str(bean.getArrivePlantAt()));
        values.put("arrivePlantGps", bean.getArrivePlantGps());
        values.put("arrivePlantGpsLoc", bean.getArrivePlantGpsLoc());
        values.put("truckState", bean.getTruckState());
        values.put("state", bean.getState());
        values.put("driverRemark", bean.getDriverRemark());
        values.put("scanPhoto", bean.getScanPhoto());
        values.put("upload", bean.getUpload());
        values.put("modifyAt", DateUtil.DateTimeToStr(new Date(), "yyyy-MM-dd HH:mm:ss"));
        db.update(DBHelper.TABLE_DOCKET, values, "id = '" + bean.getId() + "'", null);
    }

    public void updateDocketScanPhoto(Docket bean) {
        ContentValues values = new ContentValues();
        values.put("scanPhoto", bean.getScanPhoto());
        db.update(DBHelper.TABLE_DOCKET, values, "id = '" + bean.getId() + "'", null);
    }

    public void updateDocketState(DocketDto bean) {
        ContentValues values = new ContentValues();
        values.put("state", bean.getState());
        values.put("upload", bean.getUpload());
        values.put("modifyAt", DateUtil.DateTimeToStr(new Date(), "yyyy-MM-dd HH:mm:ss"));
        db.update(DBHelper.TABLE_DOCKET, values, "id = '" + bean.getId() + "'", null);
    }

    public Docket queryUnUploadDocket() {
        Docket bean = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET, null, " upload = 0 ", null, null, null, "modifyAt", "1")) {
            if (cursor.moveToNext()) {
                bean = getUnUploadDocketBean(cursor);
            }
        }
        if (bean != null) {
            String docketId = bean.getId();
            bean.setDocketPhotos(queryShortDocketPhoto(docketId));
            bean.setDocketQc(queryUnUploadQcById(docketId));
            bean.setDocketAddCharge(queryUnUploadAddChargeById(docketId));
            bean.setDocketAdditional(queryUnUploadAdditionalById(docketId));
            DocketAccRepDto accRepDto = queryUnUploadAccRep(docketId);
            if (accRepDto != null) {
                accRepDto.setDocketAccRepPhotos(queryShortAccRepPhoto(accRepDto.getId()));
                bean.setDocketAccRep(accRepDto);
            }
        }
        return bean;
    }

    private Docket getUnUploadDocketBean(Cursor cursor) {
        Docket bean = new Docket();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        String docketId = cursor.getString(cursor.getColumnIndex("id"));
        bean.setId(docketId);
        bean.setDocketNo(cursor.getString(cursor.getColumnIndex("docketNo")));
        bean.setArriveSiteAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("arriveSiteAt"))));
        bean.setArriveSiteGps(cursor.getString(cursor.getColumnIndex("arriveSiteGps")));
        bean.setArriveSiteGpsLoc(cursor.getString(cursor.getColumnIndex("arriveSiteGpsLoc")));
        bean.setStartDisAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("startDisAt"))));
        bean.setStartDisGps(cursor.getString(cursor.getColumnIndex("startDisGps")));
        bean.setStartDisGpsLoc(cursor.getString(cursor.getColumnIndex("startDisGpsLoc")));
        bean.setEndDisAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("endDisAt"))));
        bean.setEndDisGps(cursor.getString(cursor.getColumnIndex("endDisGps")));
        bean.setEndDisGpsLoc(cursor.getString(cursor.getColumnIndex("endDisGpsLoc")));
        bean.setLeaveSiteAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("leaveSiteAt"))));
        bean.setLeaveSiteGps(cursor.getString(cursor.getColumnIndex("leaveSiteGps")));
        bean.setLeaveSiteGpsLoc(cursor.getString(cursor.getColumnIndex("leaveSiteGpsLoc")));
        bean.setArrivePlantAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("arrivePlantAt"))));
        bean.setArrivePlantGps(cursor.getString(cursor.getColumnIndex("arrivePlantGps")));
        bean.setArrivePlantGpsLoc(cursor.getString(cursor.getColumnIndex("arrivePlantGpsLoc")));
        bean.setScanPhoto(cursor.getString(cursor.getColumnIndex("scanPhoto")));
        bean.setTruckState(cursor.getInt(cursor.getColumnIndex("truckState")));
        bean.setState(cursor.getInt(cursor.getColumnIndex("state")));
        bean.setDriverRemark(cursor.getString(cursor.getColumnIndex("driverRemark")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        bean.setModifyAt(DateUtil.StrToDateTime(cursor.getString(cursor.getColumnIndex("modifyAt")), "yyyy-MM-dd HH:mm:ss"));
        return bean;
    }

    public DocketDto queryDocketById(String id) {
        return queryDocket(" id = '" + id + "'");
    }

    public DocketDto queryDocketByNo(String docketNo) {
        return queryDocket(" docketNo = '" + docketNo.trim() + "'");
    }

    public DocketDto queryDocket(String where) {
        DocketDto bean = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET, null, where, null, null, null, null)) {
            if (cursor.getCount() > 0) {
                cursor.moveToNext();
                bean = getDocketBean(cursor);
            }
        }
        return bean;
    }

    private DocketDto getDocketBean(Cursor cursor) {
        DocketDto bean = new DocketDto();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        String docketId = cursor.getString(cursor.getColumnIndex("id"));
        bean.setId(docketId);
        bean.setDocketId(docketId);
        bean.setDocketNo(cursor.getString(cursor.getColumnIndex("docketNo")));
        bean.setArriveSiteAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("arriveSiteAt"))));
        bean.setArriveSiteGps(cursor.getString(cursor.getColumnIndex("arriveSiteGps")));
        bean.setArriveSiteGpsLoc(cursor.getString(cursor.getColumnIndex("arriveSiteGpsLoc")));
        bean.setStartDisAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("startDisAt"))));
        bean.setStartDisGps(cursor.getString(cursor.getColumnIndex("startDisGps")));
        bean.setStartDisGpsLoc(cursor.getString(cursor.getColumnIndex("startDisGpsLoc")));
        bean.setEndDisAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("endDisAt"))));
        bean.setEndDisGps(cursor.getString(cursor.getColumnIndex("endDisGps")));
        bean.setEndDisGpsLoc(cursor.getString(cursor.getColumnIndex("endDisGpsLoc")));
        bean.setLeaveSiteAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("leaveSiteAt"))));
        bean.setLeaveSiteGps(cursor.getString(cursor.getColumnIndex("leaveSiteGps")));
        bean.setLeaveSiteGpsLoc(cursor.getString(cursor.getColumnIndex("leaveSiteGpsLoc")));
        bean.setArrivePlantAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("arrivePlantAt"))));
        bean.setArrivePlantGps(cursor.getString(cursor.getColumnIndex("arrivePlantGps")));
        bean.setArrivePlantGpsLoc(cursor.getString(cursor.getColumnIndex("arrivePlantGpsLoc")));
        bean.setScanPhoto(cursor.getString(cursor.getColumnIndex("scanPhoto")));
        bean.setTruckState(cursor.getInt(cursor.getColumnIndex("truckState")));
        bean.setState(cursor.getInt(cursor.getColumnIndex("state")));
        bean.setDriverRemark(cursor.getString(cursor.getColumnIndex("driverRemark")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        bean.setDocketPhotos(queryShortDocketPhoto(bean.getDocketId()));
        return bean;
    }

    public DocketDto queryShortDocketById(String id) {
        DocketDto bean = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET, null, " id = '" + id + "'", null, null, null, null)) {
            if (cursor.getCount() > 0) {
                cursor.moveToNext();
                bean = getShortDocketBean(cursor);
            }
        }
        return bean;
    }

    private DocketDto getShortDocketBean(Cursor cursor) {
        DocketDto bean = new DocketDto();
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        bean.setTruckNo(cursor.getString(cursor.getColumnIndex("truckNo")));
        bean.setState(cursor.getInt(cursor.getColumnIndex("state")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        return bean;
    }

    public List<DocketDto> queryDocketList(String where) {
        List<DocketDto> list = new ArrayList<>();
//        String name = LanguageUtils.getInstance().getNameFromLan();
//        String sql = "SELECT docket.*,site." + name + " AS siteName" +
        String sql = "SELECT docket.*" +
//                ",customer." + name + " AS cusName" +
                " FROM " + DBHelper.TABLE_DOCKET + " AS docket " +
//                " LEFT JOIN " + DBHelper.TABLE_SITE + " AS site ON docket.siteId = site.id  " +
//                " LEFT JOIN " + DBHelper.TABLE_CUSTOMER + " AS customer ON docket.customerId = customer.id  " +
                " WHERE " + where;
        try (Cursor cursor = db.rawQuery(sql, null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getDocketListBean(cursor));
                }
            }
        }
        return list;
    }

    public List<String> queryDocketListId(String stateSymbol, String truckNo) {
        List<String> list = new ArrayList<>();
        try (Cursor cursor = db.rawQuery("SELECT id FROM " + DBHelper.TABLE_DOCKET +
                " WHERE truckState " + stateSymbol + " 5 AND truckNo = '" + truckNo + "' ", null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(cursor.getString(cursor.getColumnIndex("id")));
                }
            }
        }
        return list;
    }

    private DocketDto getDocketListBean(Cursor cursor) {
        DocketDto bean = new DocketDto();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        String docketId = cursor.getString(cursor.getColumnIndex("id"));
        bean.setId(docketId);
        bean.setDocketId(docketId);
        bean.setDocketNo(cursor.getString(cursor.getColumnIndex("docketNo")));
        bean.setDocketAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("docketAt"))));
        bean.setBatchTime(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("batchTime"))));
        DocketSiteDto siteDto = new DocketSiteDto();
        String siteName = cursor.getString(cursor.getColumnIndex(LanguageUtils.getInstance().getNameFromLan("site")));
        siteDto.setName(siteName == null ? "" : siteName);
        bean.setSite(siteDto);
        String extraAddress = cursor.getString(cursor.getColumnIndex("extraAddress"));
        bean.setExtraAddress(extraAddress);
        ShortDto customer = new ShortDto();
        customer.setName(cursor.getString(cursor.getColumnIndex(LanguageUtils.getInstance().getNameFromLan("cust"))));
        bean.setCustomer(customer);
        bean.setLoadQty(cursor.getDouble(cursor.getColumnIndex("loadQty")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        return bean;
    }

    public List<DocketDto> queryDocketLogList(String where) {
        List<DocketDto> list = new ArrayList<>();
        String name = LanguageUtils.getInstance().getNameFromLan();
//        String sql = "SELECT docket.*,site." + name + " AS siteName" +
        String sql = "SELECT docket.*" +
//                ",customer." + name + " AS cusName" +
                ",(SELECT COUNT(*) FROM " + DBHelper.TABLE_DOCKET_ADDITIONAL + " WHERE docketId = docket.id) AS addCount" +
                ",additional.addMileage AS addAddMileage" +
                ",addCharge.state AS addChargeState,addCharge.returnQty AS returnQty" +
                " FROM " + DBHelper.TABLE_DOCKET + " AS docket " +
//                " LEFT JOIN " + DBHelper.TABLE_SITE + " AS site ON docket.siteId = site.id  " +
//                " LEFT JOIN " + DBHelper.TABLE_CUSTOMER + " AS customer ON docket.customerId = customer.id  " +
                " LEFT JOIN " + DBHelper.TABLE_DOCKET_ADDITIONAL + " AS additional ON addCount > 0 AND docket.id = additional.docketId " +
                " LEFT JOIN " + DBHelper.TABLE_DOCKET_ADD_CHARGE + " AS addCharge ON docket.id = addCharge.docketId " +
                " WHERE " + where;
        try (Cursor cursor = db.rawQuery(sql, null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getDocketListBean(cursor));
                }
            }
        }
        return list;
    }

    public List<DocketDto> queryDocketListByAPAt(Integer status, String startAt, String endAt, String truckNo, String plantId, String siteId, String what, String limit) {
        return queryDocketLogList(getWhereByAPAt(status, startAt, endAt, truckNo, plantId, siteId, what, limit));
    }

    public int queryDocketCountByAPAt(Integer status, String startAt, String endAt, String truckNo, String plantId, String siteId, String what) {
        return queryDocketLogCount(getWhereByAPAt(status, startAt, endAt, truckNo, plantId, siteId, what, null));
    }

    public int queryDocketLogCount(String where) {
        String name = LanguageUtils.getInstance().getNameFromLan();
        int count = 0;
//        String sql = "SELECT COUNT(*) AS count,site." + name + " AS siteName" +
        String sql = "SELECT COUNT(*) AS count" +
//                ",customer." + name + " AS cusName" +
                ",(SELECT COUNT(*) FROM " + DBHelper.TABLE_DOCKET_ADDITIONAL + " WHERE docketId = docket.id) AS addCount" +
                ",additional.addMileage AS addAddMileage" +
                ",addCharge.state AS addChargeState,addCharge.returnQty AS returnQty" +
                " FROM " + DBHelper.TABLE_DOCKET + " AS docket " +
//                " LEFT JOIN " + DBHelper.TABLE_SITE + " AS site ON docket.siteId = site.id  " +
//                " LEFT JOIN " + DBHelper.TABLE_CUSTOMER + " AS customer ON docket.customerId = customer.id  " +
                " LEFT JOIN " + DBHelper.TABLE_DOCKET_ADDITIONAL + " AS additional ON addCount > 0 AND docket.id = additional.docketId " +
                " LEFT JOIN " + DBHelper.TABLE_DOCKET_ADD_CHARGE + " AS addCharge ON docket.id = addCharge.docketId " +
                " WHERE " + where;
        try (Cursor cursor = db.rawQuery(sql, null)) {
            if (cursor.getCount() > 0) {
                cursor.moveToFirst();
                count = cursor.getInt(cursor.getColumnIndex("count"));
            }
        }
        return count;
    }

    public String getWhereByAPAt(Integer status, String startAt, String endAt, String truckNo, String plantId, String siteId, String what, String limit) {
        StringBuilder where = new StringBuilder(" docket.truckState = 5 ");
        where.append("AND docket.state != 6 ");
        if (status != null) {
            switch (status) {
                case 1:
                    where.append(" AND (julianday(docket.endDisAt)-julianday(docket.batchTime))*24*60 >= (2.5*60) ");
                    break;
                case 2:
                    where.append(" AND addAddMileage is not null or trim(addAddMileage) != ''");
                    break;
                case 3:
                    where.append(" AND strftime('%H:%M',docket.batchTime) BETWEEN TIME('06:00') AND TIME('07:30')");
                    break;
                case 4:
                    where.append(" AND strftime('%H:%M',docket.batchTime) >= '20:00' OR strftime('%H:%M',docket.batchTime) <= '06:00'");
                    break;
                case 5:
                    where.append(" AND docket.state = 4 ");
                    break;
                case 6:
                    String siteName = LanguageUtils.getInstance().getNameFromLan("site");
                    where.append(" AND ").append(siteName).append(" like '%Trial%' ");
                    break;
                case 7:
                    where.append("AND addChargeState = 0");
                    break;
                case 8:
                    where.append("AND returnQty > 0");
                    break;
            }
        }
        if (TextUtils.isEmpty(startAt)) {
            startAt = DateUtil.DateTimeToStr(new Date()) + " 00:00:00";
        }
        if (TextUtils.isEmpty(endAt)) {
            endAt = DateUtil.DateTimeToStr(new Date()) + " 23:59:59";
        }
        where.append(" AND DATETIME(docket.").append(what)
                .append(") BETWEEN DATETIME('").append(startAt)
                .append("') AND DATETIME('").append(endAt).append("') ")
                .append(" AND docket.arrivePlantAt IS NOT NULL");
        if (!TextUtils.isEmpty(truckNo)) {
            where.append(" AND docket.truckNo = '").append(truckNo).append("' ");
        }
        if (!TextUtils.isEmpty(plantId)) {
            where.append(" AND docket.plantId = '").append(plantId).append("' ");
        }
        if (!TextUtils.isEmpty(siteId)) {
            where.append(" AND docket.siteId = '").append(siteId).append("' ");
        }
        if (!TextUtils.isEmpty(limit)) {
            where.append(" LIMIT ").append(limit);
        }
        return where.toString();
    }

    public List<DocketDto> queryDocketListByDocketAt(String truckNo, String limit) {
        return queryDocketList(getWhereByDocketAt(truckNo, limit));
    }

    public int queryDocketCountByDocketAt(String truckNo) {
        return queryDocketCount(getWhereByDocketAt(truckNo, null));
    }

    public String getWhereByDocketAt(String truckNo, String limit) {
        StringBuilder where = new StringBuilder(" docket.truckState < 5 ");
        Date date = new Date();
        String startAt = DateUtil.getFrontDate(date, 7) + " 00:00:00";
        String endAt = DateUtil.DateTimeToStr(date) + " 23:59:59";
        where.append(" AND DATETIME(docket.docketAt) BETWEEN DATETIME('")
                .append(startAt).append("') AND DATETIME('").append(endAt).append("') ");
        where.append(" AND docket.state != 6 ");
        if (!TextUtils.isEmpty(truckNo)) {
            where.append(" AND docket.truckNo = '").append(truckNo).append("' ");
        }
        where.append(" ORDER BY docket.createdAt DESC");
        if (!TextUtils.isEmpty(limit)) {
            where.append(" LIMIT ").append(limit);
        }
        return where.toString();
    }

    public int queryDocketCount(String where) {
        int count = 0;
        try (Cursor cursor = db.rawQuery("SELECT COUNT(*) AS count" + " FROM " + DBHelper.TABLE_DOCKET + " AS docket WHERE " + where, null)) {
            if (cursor.getCount() > 0) {
                cursor.moveToFirst();
                count = cursor.getInt(cursor.getColumnIndex("count"));
            }
        }
        return count;
    }

    public DocketDto queryDocketByDocketNo(String docketNo) {
        String where = " docket.docketNo = '" + docketNo.trim() + "' ";
        return queryDocket(where, "");
    }

    public DocketDto queryDocketOnHand(String id, String truckNo) {
        StringBuilder where = new StringBuilder("docket.truckState < 5 ");
        String orderBy = "";
        Date date = new Date();
        String firstDay = DateUtil.getFrontDate(date, 7) + " 00:00:00";
        String secondDay = DateUtil.DateTimeToStr(date) + " 23:59:59";
        where.append(" AND DATETIME(docket.docketAt) BETWEEN DATETIME('")
                .append(firstDay).append("') AND DATETIME('").append(secondDay).append("') ");
        where.append("AND docket.state != 6 ");
        if (!TextUtils.isEmpty(truckNo)) {
            where.append(" AND docket.truckNo = '").append(truckNo).append("' ");
        }
        if (TextUtils.isEmpty(id)) {
            orderBy = " ORDER BY docket.createdAt DESC LIMIT 1 ";
        } else {
            where.append(" AND docket.id = '").append(id).append("' ");
        }
        return queryDocket(where.toString(), orderBy);
    }

    public DocketDto queryDocket(String where, String orderBy) {
        DocketDto bean = null;
        try {
            String name = LanguageUtils.getInstance().getNameFromLan();
//            Cursor cursor = db.rawQuery("SELECT docket.*,site.code AS siteCode ,site." + name + " AS siteName,site.gps AS siteGps" +
            Cursor cursor = db.rawQuery("SELECT docket.*" +
//                    ",customer.code AS cusCode,customer." + name + " AS cusName" +
                    ",plant." + name + " AS plantName" +
//                    ",product." + name + " AS productName" +
                    ",cement." + name + " AS cementName" +
                    ",qc.state AS qcState,(SELECT COUNT(*) FROM " + DBHelper.TABLE_DOCKET_QC + " WHERE docketId = docket.id) AS qcCount" +
                    ",(SELECT COUNT(*) FROM " + DBHelper.TABLE_DOCKET_ACC_REP + " WHERE docketId = docket.id) AS accRepCount" +
                    ",accRep.id AS accRepId,accRep.accRepType AS accRepType ,accRep.accRepAt AS accRepAt,accRep.detail AS detail" +
                    ",(SELECT COUNT(*) FROM " + DBHelper.TABLE_DOCKET_ADDITIONAL + " WHERE docketId = docket.id) AS addCount" +
                    ",additional.id AS addId,additional.slump AS addSlump,additional.temperature AS addTemperature" +
                    ",additional.takeSample AS addTakeSample,additional.addMileage AS addAddMileage" +
                    ",additional.addType AS addAddType,additional.addRemark AS addAddRemark" +
                    " FROM " + DBHelper.TABLE_DOCKET + " AS docket " +
//                    " LEFT JOIN " + DBHelper.TABLE_SITE + " AS site ON docket.siteId = site.id  " +
//                    " LEFT JOIN " + DBHelper.TABLE_CUSTOMER + " AS customer ON docket.customerId = customer.id  " +
                    " LEFT JOIN " + DBHelper.TABLE_PLANT + " AS plant ON docket.plantId = plant.id  " +
//                    " LEFT JOIN " + DBHelper.TABLE_PRODUCT + " AS product ON docket.productId = product.id  " +
                    " LEFT JOIN " + DBHelper.TABLE_CEMENT + " AS cement ON docket.cementId = cement.id  " +
                    " LEFT JOIN " + DBHelper.TABLE_DOCKET_QC + " AS qc ON docket.id = qc.docketId " +
                    " LEFT JOIN " + DBHelper.TABLE_DOCKET_ACC_REP + " AS accRep ON accRepCount > 0 AND docket.id = accRep.docketId " +
                    " LEFT JOIN " + DBHelper.TABLE_DOCKET_ADDITIONAL + " AS additional ON addCount > 0 AND docket.id = additional.docketId " +
                    " WHERE " + where + orderBy, null);
            if (cursor.moveToNext()) {
                bean = getDocketOnHandBean(cursor, false);
                cursor.close();
                bean.setAdmixtures(queryDocketAdmixtureFromId(bean.getDocketId(), name));
                bean.setDocketPhotos(queryShortDocketPhoto(bean.getDocketId()));
                if (bean.getAccRep() != null) {
                    bean.getAccRep().setDocketAccRepPhotos(queryShortAccRepPhoto(bean.getAccRep().getId()));
                }
            } else {
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bean;
    }

    private DocketDto getDocketOnHandBean(Cursor cursor, boolean isLog) {
        DocketDto bean = new DocketDto();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        String docketId = cursor.getString(cursor.getColumnIndex("id"));
        bean.setId(docketId);
        bean.setDocketId(docketId);
        bean.setDocketNo(cursor.getString(cursor.getColumnIndex("docketNo")));
        DocketSiteDto siteDto = new DocketSiteDto();
        siteDto.setCode(cursor.getString(cursor.getColumnIndex("siteCode")));
        siteDto.setGps(cursor.getString(cursor.getColumnIndex("siteGPS")));
        String siteName = cursor.getString(cursor.getColumnIndex(LanguageUtils.getInstance().getNameFromLan("site")));
        siteDto.setName(siteName == null ? "" : siteName);
        bean.setSite(siteDto);
        String extraAddress = cursor.getString(cursor.getColumnIndex("extraAddress"));
        bean.setExtraAddress(extraAddress);
        ShortDto customer = new ShortDto();
        customer.setCode(cursor.getString(cursor.getColumnIndex("customerCode")));
        customer.setName(cursor.getString(cursor.getColumnIndex(LanguageUtils.getInstance().getNameFromLan("cust"))));
        bean.setCustomer(customer);
        bean.setContactTel(cursor.getString(cursor.getColumnIndex("contactTel")));
        bean.setContactPerson(cursor.getString(cursor.getColumnIndex("contactPerson")));
        bean.setTruckNo(cursor.getString(cursor.getColumnIndex("truckNo")));
        ShortDto plant = new ShortDto();
        plant.setName(cursor.getString(cursor.getColumnIndex("plantName")));
        bean.setPlant(plant);
        ShortDto product = new ShortDto();
        product.setName(cursor.getString(cursor.getColumnIndex(LanguageUtils.getInstance().getNameFromLan("product"))));
        bean.setProduct(product);
        bean.setSlump(cursor.getDouble(cursor.getColumnIndex("slump")));
        bean.setLoadQty(cursor.getDouble(cursor.getColumnIndex("loadQty")));
        bean.setCumQty(cursor.getDouble(cursor.getColumnIndex("cumQty")));
        bean.setOrderQty(cursor.getDouble(cursor.getColumnIndex("orderQty")));
        bean.setDocketAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("docketAt"))));
        bean.setBatchTime(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("batchTime"))));
        ShortDto cement = new ShortDto();
        cement.setName(cursor.getString(cursor.getColumnIndex("cementName")));
        bean.setCement(cement);
        bean.setCustPo(cursor.getString(cursor.getColumnIndex("custPo")));
        bean.setNominalMaxAgg(cursor.getString(cursor.getColumnIndex("nominalMaxAgg")));
        bean.setSpecialTerm(cursor.getString(cursor.getColumnIndex("specialTerm")));
        bean.setAdditionalCharge1(cursor.getString(cursor.getColumnIndex("additionalCharge1")));
        bean.setAdditionalCharge2(cursor.getString(cursor.getColumnIndex("additionalCharge2")));
        bean.setLastDriverTel(cursor.getString(cursor.getColumnIndex("lastDriverTel")));
        bean.setPreTruckNo(cursor.getString(cursor.getColumnIndex("preTruckNo")));
        bean.setkWahQRCode(cursor.getString(cursor.getColumnIndex("kWahQRCode")));
        bean.setCustQRCode(cursor.getString(cursor.getColumnIndex("custQRCode")));
        bean.setAuthorityQRCode(cursor.getString(cursor.getColumnIndex("authorityQRCode")));
        bean.setArriveSiteAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("arriveSiteAt"))));
        bean.setArriveSiteGps(cursor.getString(cursor.getColumnIndex("arriveSiteGps")));
        bean.setStartDisAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("startDisAt"))));
        bean.setStartDisGps(cursor.getString(cursor.getColumnIndex("startDisGps")));
        bean.setEndDisAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("endDisAt"))));
        bean.setEndDisGps(cursor.getString(cursor.getColumnIndex("endDisGps")));
        bean.setLeaveSiteAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("leaveSiteAt"))));
        bean.setLeaveSiteGps(cursor.getString(cursor.getColumnIndex("leaveSiteGps")));
        bean.setArrivePlantAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("arrivePlantAt"))));
        bean.setArrivePlantGps(cursor.getString(cursor.getColumnIndex("arrivePlantGps")));
        bean.setTruckState(cursor.getInt(cursor.getColumnIndex("truckState")));
        bean.setState(cursor.getInt(cursor.getColumnIndex("state")));
        bean.setRemark(cursor.getString(cursor.getColumnIndex("remark")));
        bean.setDriverRemark(cursor.getString(cursor.getColumnIndex("driverRemark")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        if (cursor.getInt(cursor.getColumnIndex("addCount")) > 0) {
            DocketAdditionalDto additional = new DocketAdditionalDto();
            additional.setId(cursor.getString(cursor.getColumnIndex("addId")));
            additional.setAddRemark(cursor.getString(cursor.getColumnIndex("addAddRemark")));
            additional.setSlump(cursor.getDouble(cursor.getColumnIndex("addSlump")));
            additional.setTemperature(cursor.getDouble(cursor.getColumnIndex("addTemperature")));
            additional.setTakeSample(cursor.getInt(cursor.getColumnIndex("addTakeSample")) == 1);
            additional.setAddMileage(cursor.getInt(cursor.getColumnIndex("addAddMileage")));
            additional.setAddType(cursor.getInt(cursor.getColumnIndex("addAddType")));
            bean.setAdditional(additional);
        }
        if (isLog) {
            bean.setQcPhoto(cursor.getString(cursor.getColumnIndex("qcPhoto")));
            bean.setAddcPhoto(cursor.getString(cursor.getColumnIndex("addcPhoto")));
        } else {
            bean.setQcState(cursor.getInt(cursor.getColumnIndex("qcCount")) == 0 ? -1 : cursor.getInt(cursor.getColumnIndex("qcState")));
            if (cursor.getInt(cursor.getColumnIndex("accRepCount")) > 0) {
                DocketAccRepDto accRep = new DocketAccRepDto();
                accRep.setId(cursor.getString(cursor.getColumnIndex("accRepId")));
                accRep.setDocketId(docketId);
                accRep.setDetail(cursor.getString(cursor.getColumnIndex("detail")));
                accRep.setAccRepType(cursor.getInt(cursor.getColumnIndex("accRepType")));
                accRep.setAccRepAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("accRepAt"))));
                bean.setAccRep(accRep);
            }
        }
        return bean;
    }

    public DocketDto queryDocketLogById(String docketId) {
        DocketDto bean = null;
        try {
            String name = LanguageUtils.getInstance().getNameFromLan();
//            String sql = "SELECT docket.*,site.code AS siteCode ,site." + name + " AS siteName,site.gps AS siteGps" +
            String sql = "SELECT docket.*" +
//                    ",customer.code AS cusCode,customer." + name + " AS cusName" +
                    ",plant." + name + " AS plantName" +
//                    ",product." + name + " AS productName" +
                    ",cement." + name + " AS cementName" +
                    ",qc.photo AS qcPhoto" +
                    ",addCharge.photo AS addcPhoto" +
                    ",(SELECT COUNT(*) FROM " + DBHelper.TABLE_DOCKET_ADDITIONAL + " WHERE docketId = docket.id) AS addCount" +
                    ",additional.id AS addId,additional.slump AS addSlump,additional.temperature AS addTemperature" +
                    ",additional.takeSample AS addTakeSample,additional.addMileage AS addAddMileage" +
                    ",additional.addType AS addAddType,additional.addRemark AS addAddRemark" +
                    " FROM " + DBHelper.TABLE_DOCKET + " AS docket " +
//                    " LEFT JOIN " + DBHelper.TABLE_SITE + " AS site ON docket.siteId = site.id  " +
//                    " LEFT JOIN " + DBHelper.TABLE_CUSTOMER + " AS customer ON docket.customerId = customer.id  " +
                    " LEFT JOIN " + DBHelper.TABLE_PLANT + " AS plant ON docket.plantId = plant.id  " +
//                    " LEFT JOIN " + DBHelper.TABLE_PRODUCT + " AS product ON docket.productId = product.id  " +
                    " LEFT JOIN " + DBHelper.TABLE_CEMENT + " AS cement ON docket.cementId = cement.id  " +
                    " LEFT JOIN " + DBHelper.TABLE_DOCKET_QC + " AS qc ON docket.id = qc.docketId " +
                    " LEFT JOIN " + DBHelper.TABLE_DOCKET_ADD_CHARGE + " AS addCharge ON docket.id = addCharge.docketId " +
                    " LEFT JOIN " + DBHelper.TABLE_DOCKET_ADDITIONAL + " AS additional ON addCount > 0 AND docket.id = additional.docketId " +
                    " WHERE docket.id = '" + docketId + "' ";
            Cursor cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                bean = getDocketOnHandBean(cursor, true);
                cursor.close();
                bean.setAdmixtures(queryDocketAdmixtureFromId(bean.getDocketId(), name));
                bean.setDocketPhotos(queryShortDocketPhoto(bean.getDocketId()));
            } else {
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bean;
    }

    public Date queryDocketModifyAt(int id) {
        Date createdAt = null;
        Cursor cursor = db.query(DBHelper.TABLE_DOCKET, new String[]{"modifyAt"}, "_id = " + id, null, null, null, null);
        if (cursor.moveToFirst()) {
            createdAt = DateUtil.StrToDateTime(cursor.getString(cursor.getColumnIndex("modifyAt")), "yyyy-MM-dd HH:mm:ss");
        }
        cursor.close();
        return createdAt;
    }

    public List<DocketDto> queryDocketByCreateAt(String filterTime) {
        List<DocketDto> list = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET, null,
                "upload = 1 AND STRFTIME('%Y-%m-%d',createdAt) < '" + filterTime + "'",
                null, null, null, null)) {
            if (cursor.getCount() > 0) {
                list = new ArrayList<>();
                while (cursor.moveToNext()) {
                    list.add(getShortDocket(cursor));
                }
            }
        }
        return list;
    }

    private DocketDto getShortDocket(Cursor cursor) {
        DocketDto bean = new DocketDto();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        String docketId = cursor.getString(cursor.getColumnIndex("id"));
        bean.setId(docketId);
        bean.setDocketId(docketId);
        return bean;
    }

    public void deleteOldDocket(String filterTime) {
        db.delete(DBHelper.TABLE_DOCKET, " STRFTIME('%Y-%m-%d',createdAt) < '" + filterTime + "'", null);
    }

    public void deleteDocket(String id) {
        db.delete(DBHelper.TABLE_DOCKET, " id = '" + id + "'", null);
    }

    public void insertAccRep(DocketAccRepDto bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("docketId", bean.getDocketId());
        values.put("accRepType", bean.getAccRepType());
        values.put("accRepAt", DateUtil.DateTime2Str(bean.getAccRepAt()));
        values.put("detail", bean.getDetail());
        values.put("gps", bean.getGps());
        values.put("gpsLoc", bean.getGpsLoc());
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_DOCKET_ACC_REP, null, values);
    }

    public void updateAccRepByDocketId(DocketAccRepDto bean) {
        ContentValues values = new ContentValues();
        values.put("accRepType", bean.getAccRepType());
        values.put("accRepAt", DateUtil.DateTime2Str(bean.getAccRepAt()));
        values.put("detail", bean.getDetail());
        values.put("gps", bean.getGps());
        values.put("gpsLoc", bean.getGpsLoc());
        values.put("upload", bean.getUpload());
        db.update(DBHelper.TABLE_DOCKET_ACC_REP, values, " docketId = '" + bean.getDocketId() + "'", null);
    }

    public DocketAccRepDto queryAccRepByDocketId(String id) {
        return queryAccRep(" docketId = '" + id + "' ");
    }

    public DocketAccRepDto queryUnUploadAccRep(String id) {
        return queryAccRep(" docketId = '" + id + "' ");
    }

    public DocketAccRepDto queryAccRep(String where) {
        DocketAccRepDto bean = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET_ACC_REP, null, where, null, null, null, null)) {
            if (cursor.moveToNext()) {
                bean = getAccRep(cursor);
            }
        }
        return bean;
    }

    private DocketAccRepDto getAccRep(Cursor cursor) {
        DocketAccRepDto bean = new DocketAccRepDto();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        bean.setDocketId(cursor.getString(cursor.getColumnIndex("docketId")));
        bean.setAccRepType(cursor.getInt(cursor.getColumnIndex("accRepType")));
        bean.setAccRepAt(DateUtil.Str2DateTime(cursor.getString(cursor.getColumnIndex("accRepAt"))));
        bean.setDetail(cursor.getString(cursor.getColumnIndex("detail")));
        bean.setGps(cursor.getString(cursor.getColumnIndex("gps")));
        bean.setGpsLoc(cursor.getString(cursor.getColumnIndex("gpsLoc")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        return bean;
    }

    private DocketAccRepDto getShortAccRep(Cursor cursor) {
        DocketAccRepDto bean = new DocketAccRepDto();
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        return bean;
    }

    public void deleteAccRepByDocketId(String id) {
        db.delete(DBHelper.TABLE_DOCKET_ACC_REP, " docketId = '" + id + "'", null);
    }

    public void deleteOldAccRep(String filterTime) {
        db.delete(DBHelper.TABLE_DOCKET_ACC_REP, " EXISTS(" + getOldDocketIdSql(filterTime) + " and " + DBHelper.TABLE_DOCKET_ACC_REP + ".docketId = b.id )", null);
    }

    public void insertAccRepPhoto(DocketAccRepPhoto bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("accRepId", bean.getAccRepId());
        values.put("photo", bean.getPhoto());
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_DOCKET_ACC_REP_PHOTO, null, values);
    }

    public void updateAccRepPhoto(DocketAccRepPhoto bean) {
        ContentValues values = new ContentValues();
        values.put("photo", bean.getPhoto());
        values.put("accRepId", bean.getAccRepId());
        values.put("upload", bean.getUpload());
        db.update(DBHelper.TABLE_DOCKET_ACC_REP_PHOTO, values, " _id = " + bean.get_id(), null);
    }

    public void deleteAccRepPhoto(String accRepId) {
        db.delete(DBHelper.TABLE_DOCKET_ACC_REP_PHOTO, " accRepId = '" + accRepId + "'", null);
    }

    public void deleteOldAccRepPhotos(String filterTime) {
        db.delete(DBHelper.TABLE_DOCKET_ACC_REP_PHOTO, " EXISTS(SELECT id FROM " + DBHelper.TABLE_DOCKET_ACC_REP + " a WHERE a.id = " + DBHelper.TABLE_DOCKET_ACC_REP_PHOTO + ".accRepId AND EXISTS(" + getOldDocketIdSql(filterTime) + " AND a.docketId = b.id ))", null);
    }

    public List<DocketPhoto> queryShortAccRepPhoto(String accRepId) {
        List<DocketPhoto> list = new ArrayList<>();
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET_ACC_REP_PHOTO, new String[]{"_id", "photo"},
                " accRepId = '" + accRepId + "'", null, null, null, null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    DocketPhoto bean = new DocketPhoto();
                    bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
                    bean.setPhoto(cursor.getString(cursor.getColumnIndex("photo")));
                    list.add(bean);
                }
            }
        }
        return list;
    }

    public void insertAddCharge(DocketAddChargeDto bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("docketId", bean.getDocketId());
        values.put("acceptedQty", bean.getAcceptedQty());
        values.put("rejectQty", bean.getRejectQty());
        values.put("rejectReason", bean.getRejectReason());
        values.put("rejectRemark", bean.getRejectRemark());
        values.put("rejectVal", bean.getRejectVal());
        values.put("returnQty", bean.getReturnQty());
        values.put("payDumpCharge", bean.getPayDumpCharge());
        values.put("payMultipleDrop", bean.getPayMultipleDrop());
        values.put("payOther", bean.getPayOther());
        values.put("payOtherRemark", bean.getPayOtherRemark());
        values.put("mileage", bean.getMileage());
        values.put("takeSample", bean.getTakeSample());
        values.put("photo", bean.getPhoto());
        values.put("photo1", bean.getPhoto1());
        values.put("state", bean.getState());
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_DOCKET_ADD_CHARGE, null, values);
    }

    public void updateAddChargePhoto(DocketAddChargeDto bean) {
        ContentValues values = new ContentValues();
        values.put("photo", bean.getPhoto());
        values.put("photo1", bean.getPhoto1());
        db.update(DBHelper.TABLE_DOCKET_ADD_CHARGE, values, " docketId = '" + bean.getDocketId() + "'", null);
    }

    public DocketAddChargeDto queryUnUploadAddChargeById(String id) {
        DocketAddChargeDto bean = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET_ADD_CHARGE, null,
                " docketId = '" + id + "' ", null, null, null, null)) {
            if (cursor.moveToNext()) {
                bean = getAddCharge(cursor);
            }
        }
        return bean;
    }

    public void deleteAddChargeByDocketId(String id) {
        db.delete(DBHelper.TABLE_DOCKET_ADD_CHARGE, " docketId = '" + id + "'", null);
    }

    public void deleteOldAddCharge(String filterTime) {
        db.delete(DBHelper.TABLE_DOCKET_ADD_CHARGE, " EXISTS(" + getOldDocketIdSql(filterTime) + " and " + DBHelper.TABLE_DOCKET_ADD_CHARGE + ".docketId = b.id )", null);
    }

    private DocketAddChargeDto getAddCharge(Cursor cursor) {
        DocketAddChargeDto bean = new DocketAddChargeDto();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        bean.setDocketId(cursor.getString(cursor.getColumnIndex("docketId")));
        bean.setAcceptedQty(cursor.getDouble(cursor.getColumnIndex("acceptedQty")));
        bean.setRejectQty(cursor.getDouble(cursor.getColumnIndex("rejectQty")));
        bean.setRejectReason(cursor.getInt(cursor.getColumnIndex("rejectReason")));
        bean.setRejectRemark(cursor.getString(cursor.getColumnIndex("rejectRemark")));
        bean.setRejectVal(cursor.getDouble(cursor.getColumnIndex("rejectVal")));
        bean.setReturnQty(cursor.getDouble(cursor.getColumnIndex("returnQty")));
        bean.setPayDumpCharge(cursor.getInt(cursor.getColumnIndex("payDumpCharge")) == 1);
        bean.setPayMultipleDrop(cursor.getInt(cursor.getColumnIndex("payMultipleDrop")) == 1);
        bean.setPayOther(cursor.getInt(cursor.getColumnIndex("payOther")) == 1);
        bean.setPayOtherRemark(cursor.getString(cursor.getColumnIndex("payOtherRemark")));
        bean.setMileage(cursor.getInt(cursor.getColumnIndex("mileage")));
        bean.setTakeSample(cursor.getInt(cursor.getColumnIndex("takeSample")) == 1);
        bean.setPhoto(cursor.getString(cursor.getColumnIndex("photo")));
        bean.setPhoto1(cursor.getString(cursor.getColumnIndex("photo1")));
        bean.setState(cursor.getInt(cursor.getColumnIndex("state")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        return bean;
    }

    public void insertAdditional(DocketAdditionalDto bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("docketId", bean.getDocketId());
        values.put("slump", bean.getSlump());
        values.put("temperature", bean.getTemperature());
        values.put("takeSample", bean.getTakeSample());
        values.put("addMileage", bean.getAddMileage());
        values.put("addType", bean.getAddType());
        values.put("addRemark", bean.getAddRemark());
        values.put("gps", bean.getGps());
        values.put("gpsLoc", bean.getGpsLoc());
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_DOCKET_ADDITIONAL, null, values);
    }

    public void updateAdditional(DocketAdditionalDto bean) {
        ContentValues values = new ContentValues();
        values.put("slump", bean.getSlump());
        values.put("temperature", bean.getTemperature());
        values.put("takeSample", bean.getTakeSample() ? "1" : "0");
        values.put("addMileage", bean.getAddMileage());
        values.put("addType", bean.getAddType());
        values.put("addRemark", bean.getAddRemark());
        values.put("gps", bean.getGps());
        values.put("gpsLoc", bean.getGpsLoc());
        values.put("upload", bean.getUpload());
        db.update(DBHelper.TABLE_DOCKET_ADDITIONAL, values, " docketId = '" + bean.getDocketId() + "' ", null);
    }

    public void deleteAdditionalByDocketId(String id) {
        db.delete(DBHelper.TABLE_DOCKET_ADDITIONAL, " docketId = '" + id + "'", null);
    }

    public void deleteOldAdditional(String filterTime) {
        db.delete(DBHelper.TABLE_DOCKET_ADDITIONAL, " EXISTS(" + getOldDocketIdSql(filterTime) + " and " + DBHelper.TABLE_DOCKET_ADDITIONAL + ".docketId = b.id )", null);
    }

    public DocketAdditionalDto queryAdditionalByDocketId(String id) {
        return queryAdditional(" docketId = '" + id + "'");
    }

    public DocketAdditionalDto queryUnUploadAdditionalById(String id) {
        return queryAdditional(" docketId = '" + id + "'");
    }

    public DocketAdditionalDto queryAdditional(String where) {
        DocketAdditionalDto bean = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET_ADDITIONAL, null,
                where, null, null, null, null)) {
            if (cursor.moveToNext()) {
                bean = getAdditional(cursor);
            }
        }
        return bean;
    }

    private DocketAdditionalDto getAdditional(Cursor cursor) {
        DocketAdditionalDto bean = new DocketAdditionalDto();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        bean.setDocketId(cursor.getString(cursor.getColumnIndex("docketId")));
        bean.setSlump(cursor.getDouble(cursor.getColumnIndex("slump")));
        bean.setTemperature(cursor.getDouble(cursor.getColumnIndex("temperature")));
        bean.setTakeSample(cursor.getInt(cursor.getColumnIndex("takeSample")) == 1);
        bean.setAddMileage(cursor.getInt(cursor.getColumnIndex("addMileage")));
        bean.setAddType(cursor.getInt(cursor.getColumnIndex("addType")));
        bean.setAddRemark(cursor.getString(cursor.getColumnIndex("addRemark")));
        bean.setGps(cursor.getString(cursor.getColumnIndex("gps")));
        bean.setGpsLoc(cursor.getString(cursor.getColumnIndex("gpsLoc")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        return bean;
    }

    public void insertDocketAdmixture(DocketAdmixture bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("docketId", bean.getDocketId());
        values.put("admixtureId", bean.getAdmixtureId());
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_DOCKET_ADMIXTURE, null, values);
    }

    public List<ShortDto> queryDocketAdmixtureFromId(String docketId, String name) {
        List<ShortDto> list = new ArrayList<>();
        String sql = "SELECT docketAdmixture.admixtureId" +
                ",admixture." + name + " AS admixtureName,admixture.code AS admixtureCode" +
                " FROM " + DBHelper.TABLE_DOCKET_ADMIXTURE + " AS docketAdmixture " +
                " LEFT JOIN " + DBHelper.TABLE_ADMIXTURE + " AS admixture ON admixture.id = docketAdmixture.admixtureId " +
                " WHERE docketAdmixture.docketId = '" + docketId + "'";
        try (Cursor cursor = db.rawQuery(sql, null)) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    list.add(getShortDocketAdmixture(cursor));
                }
            }
        }
        return list;
    }

    private ShortDto getShortDocketAdmixture(Cursor cursor) {
        ShortDto bean = new ShortDto();
        bean.setId(cursor.getString(cursor.getColumnIndex("admixtureId")));
        bean.setName(cursor.getString(cursor.getColumnIndex("admixtureName")));
        bean.setCode(cursor.getString(cursor.getColumnIndex("admixtureCode")));
        return bean;
    }

    public void deleteOldDocketAdmixture(String filterTime) {
        db.delete(DBHelper.TABLE_DOCKET_ADMIXTURE, " EXISTS(" + getOldDocketIdSql(filterTime) + " and " + DBHelper.TABLE_DOCKET_ADMIXTURE + ".docketId = b.id )", null);
    }

    public void deleteDocketAdmixtureById(String id) {
        db.delete(DBHelper.TABLE_DOCKET_ADMIXTURE, " docketId = '" + id + "'", null);
    }

    public void insertDocketPhoto(DocketPhoto bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("docketId", bean.getDocketId());
        values.put("photo", bean.getPhoto());
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_DOCKET_PHOTO, null, values);
    }

    public void updateDocketPhoto(DocketPhoto bean) {
        ContentValues values = new ContentValues();
        values.put("photo", bean.getPhoto());
        values.put("upload", bean.getUpload());
        db.update(DBHelper.TABLE_DOCKET_PHOTO, values, " _id = " + bean.get_id(), null);
    }

    public void updateDocketPhoto(String docketId, int upload) {
        ContentValues values = new ContentValues();
        values.put("upload", upload);
        db.update(DBHelper.TABLE_DOCKET_PHOTO, values, " docketId = '" + docketId + "'", null);
    }

    public void deleteDocketPhoto(String docketId) {
        db.delete(DBHelper.TABLE_DOCKET_PHOTO, " docketId = '" + docketId + "'", null);
    }

    public void deleteOldDocketPhoto(String filterTime) {
        db.delete(DBHelper.TABLE_DOCKET_PHOTO, " EXISTS(" + getOldDocketIdSql(filterTime) + " and " + DBHelper.TABLE_DOCKET_PHOTO + ".docketId = b.id )", null);
    }

    private String getOldDocketIdSql(String filterTime) {
        return "SELECT id from " + DBHelper.TABLE_DOCKET + " b WHERE STRFTIME('%Y-%m-%d',createdAt) < '" + filterTime + "'";
    }

    public List<DocketPhoto> queryShortDocketPhoto(String docketId) {
        List<DocketPhoto> list = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET_PHOTO, new String[]{"_id", "photo"},
                " docketId = '" + docketId + "'", null, null, null, null)) {
            if (cursor.getCount() > 0) {
                list = new ArrayList<>();
                while (cursor.moveToNext()) {
                    DocketPhoto bean = new DocketPhoto();
                    bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
                    bean.setPhoto(cursor.getString(cursor.getColumnIndex("photo")));
                    list.add(bean);
                }
            }
        }
        return list;
    }

    public void insertDocketQc(DocketQcDto bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("docketId", bean.getDocketId());
        values.put("rejectReason", bean.getRejectReason());
        values.put("rejectVal", bean.getRejectVal());
        values.put("rejectRemak", bean.getRejectRemak());
        values.put("photo", bean.getPhoto());
        values.put("photo1", bean.getPhoto1());
        values.put("state", bean.getState());
        values.put("gps", bean.getGps());
        values.put("gpsLoc", bean.getGpsLoc());
        values.put("upload", bean.getUpload());
        db.insert(DBHelper.TABLE_DOCKET_QC, null, values);
    }

    public void updateDocketQc(DocketQcDto bean) {
        ContentValues values = new ContentValues();
        values.put("photo", bean.getPhoto());
        values.put("photo1", bean.getPhoto1());
        db.update(DBHelper.TABLE_DOCKET_QC, values, " docketId = '" + bean.getDocketId() + "'", null);
    }

    public DocketQcDto queryUnUploadQcById(String id) {
        DocketQcDto bean = null;
        try (Cursor cursor = db.query(DBHelper.TABLE_DOCKET_QC, null,
                " docketId = '" + id + "' ", null, null, null, null)) {
            if (cursor.moveToNext()) {
                bean = getDocketQc(cursor);
            }
        }
        return bean;
    }

    private DocketQcDto getDocketQc(Cursor cursor) {
        DocketQcDto bean = new DocketQcDto();
        bean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
        bean.setId(cursor.getString(cursor.getColumnIndex("id")));
        bean.setDocketId(cursor.getString(cursor.getColumnIndex("docketId")));
        bean.setRejectReason(cursor.getInt(cursor.getColumnIndex("rejectReason")));
        bean.setRejectVal(cursor.getDouble(cursor.getColumnIndex("rejectVal")));
        bean.setRejectRemak(cursor.getString(cursor.getColumnIndex("rejectRemak")));
        bean.setPhoto(cursor.getString(cursor.getColumnIndex("photo")));
        bean.setPhoto1(cursor.getString(cursor.getColumnIndex("photo1")));
        bean.setState(cursor.getInt(cursor.getColumnIndex("state")));
        bean.setGps(cursor.getString(cursor.getColumnIndex("gps")));
        bean.setGpsLoc(cursor.getString(cursor.getColumnIndex("gpsLoc")));
        bean.setUpload(cursor.getInt(cursor.getColumnIndex("upload")));
        return bean;
    }

    public void deleteQcByDocketId(String id) {
        db.delete(DBHelper.TABLE_DOCKET_QC, " docketId = '" + id + "'", null);
    }

    public void deleteOldQc(String filterTime) {
        db.delete(DBHelper.TABLE_DOCKET_QC, " EXISTS(" + getOldDocketIdSql(filterTime) + " and " + DBHelper.TABLE_DOCKET_QC + ".docketId = b.id )", null);
    }

    public void insertMenu(MenuDto bean) {
        ContentValues values = new ContentValues();
        values.put("id", bean.getId());
        values.put("pId", bean.getpId());
        values.put("cnName", bean.getCnName());
        values.put("enName", bean.getEnName());
        values.put("typeName", bean.getTypeName());
        values.put("appAct", bean.getAppAct());
        values.put("appIcon", bean.getAppIcon());
        values.put("color", bean.getColor());
        values.put("viewFlag", bean.getViewFlag());
        values.put("quickFlag", bean.getQuickFlag());
        values.put("powerFlag", bean.getPowerFlag());
        values.put("state", bean.getState());
        values.put("operFlag", bean.getOperFlag());
        values.put("orderId", bean.getOrderId());
        values.put("gpFlag", bean.getGpFlag());
        values.put("hasChildren", bean.getHasChildren());
        db.insert(DBHelper.TABLE_MENU, null, values);
    }

    public List<MenuDto> queryMenu(String pid, String quick, String viewFlag) {
        List<MenuDto> list = new ArrayList<>();
        String name = LanguageUtils.getInstance().getNameFromLan();
        try (Cursor cursor = db.query(DBHelper.TABLE_MENU, new String[]{name, "appAct", "id", "operFlag"},
                " pid = '" + pid + "' AND quickFlag = '" + quick + "' AND viewFlag = '" + viewFlag + "'", null, null, null, " orderId ")) {
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    MenuDto bean = new MenuDto();
                    bean.setId(cursor.getString(cursor.getColumnIndex("id")));
                    bean.setpId(pid);
                    bean.setName(cursor.getString(cursor.getColumnIndex(name)));
                    bean.setUrl(cursor.getString(cursor.getColumnIndex("appAct")));
                    bean.setOperFlag(cursor.getInt(cursor.getColumnIndex("operFlag")));
                    list.add(bean);
                }
            }
        }
        return list;
    }

    public int queryTableById(String table, String id) {
        int count;
        try (Cursor cursor = db.query(table, new String[]{"id"}, " id = '" + id + "'", null, null, null, null, "1")) {
            count = cursor.getCount();
        }
        return count;
    }

    public void updateUpload(String table, int id, int upload) {
        ContentValues values = new ContentValues();
        values.put("upload", upload);
        db.update(table, values, "_id = " + id, null);
    }

    public void delete(String table) {
        db.delete(table, null, null);
    }

    public void deleteTableAndSequence(String table) {
        db.delete(table, null, null);
        db.delete(DBHelper.SYS_TABLE_SEQUENCE, "name = '" + table + "'", null);
    }

    public void close() {
        if (db != null) {
            db.close();
            db = null;
        }
    }
}
